﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

namespace Our.Umbraco.uDbCompare
{
    public partial class TemplateInUse : System.Web.UI.UserControl
    {
        public bool UseRemoteConnectionString { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
                BindData();
        }

        private string connectionString
        {
            get
            {
                string connectionString = uDbCompare.CurrentConnectionString;
                if (UseRemoteConnectionString)
                    connectionString = uDbCompare.RemoteConnectionString;
                return connectionString;
            }
        }

        public void BindData()
        {
            if (uDbCompare.IsValidDbConnection(UseRemoteConnectionString))
            {
                DataTable templateTable = uDbCompare.QueryDb("select nodeId, alias from cmsTemplate", connectionString);
                templateTable.DefaultView.Sort = "alias asc";
                TemplateDropDownList.DataTextField = "alias";
                TemplateDropDownList.DataValueField = "nodeId";
                TemplateDropDownList.DataSource = templateTable;
                TemplateDropDownList.DataBind();

                SearchButton.Enabled = true;
                UnusedButton.Enabled = true;
            }
        }

        protected void TemplateButton_OnClick(object sender, EventArgs e)
        {
            DataTable matchDt = FindTemplate(TemplateDropDownList.SelectedValue);
            TemplateGridView.DataSource = matchDt;
            TemplateGridView.DataBind();

            NoResultsLabel.Visible = (matchDt.Rows.Count == 0);
        }

        protected void UnusedButton_OnClick(object sender, EventArgs e)
        {
            DataTable matchDt = FindUnusedTemplates();
            TemplateGridView.DataSource = matchDt;
            TemplateGridView.DataBind();

            NoResultsLabel.Visible = (matchDt.Rows.Count == 0);
        }

        private DataTable FindUnusedTemplates()
        {
            string templateQuery = @"select Template.alias as TemplateAlias, template.nodeid as TemplateNodeId, dt.IsDefault as IsDefaultTemplate, 
                                        ct.alias as ContentTypeAlias, ct.nodeId as ContentTypeNodeId, n.text as ContentNodeName, n.id as ContentNodeId,
                                        n.path as ContentNodePath, case when count(childtemplate.nodeid) > 0 then 1 else 0 end as HasTemplateChildren
                                        from cmsTemplate Template
			                            left outer join umbracoNode TemplateNode on TemplateNode.id = Template.nodeId
			                            left outer join cmsDocumentType dt on dt.templateNodeId = TemplateNode.id 
			                            left outer join cmsContentType ct  on dt.contenttypenodeid = ct.nodeid
			                            left outer join cmscontent c on ct.nodeid = c.contenttype
			                            left outer join umbracoNode n on c.nodeid = n.id
			                            left outer join cmsTemplate ChildTemplate on Template.nodeId = ChildTemplate.master
                                        where n.id is null 		                            
                                        group by Template.alias, template.nodeid, dt.IsDefault, 
                                                ct.alias , ct.nodeId, n.text, n.id, n.path
                                        having count(childtemplate.nodeid) = 0
                                        ";
            DataTable dt = uDbCompare.QueryDb(templateQuery, connectionString);

            return dt;
        }

        private DataTable FindTemplate(string alias)
        {
            string templateQuery = @"select Template.alias as TemplateAlias, template.nodeid as TemplateNodeId, dt.IsDefault as IsDefaultTemplate, 
                                        ct.alias as ContentTypeAlias, ct.nodeId as ContentTypeNodeId, n.text as ContentNodeName, n.id as ContentNodeId,
                                        n.path as ContentNodePath, case when d.templateId = Template.nodeId then 'Yes' else 'No' end as ContentUsingTemplate,
                                        case when count(childtemplate.nodeid) > 0 then 1 else 0 end as HasTemplateChildren
                                        from cmsTemplate Template
			                            left outer join umbracoNode TemplateNode on TemplateNode.id = Template.nodeId
			                            left outer join cmsDocumentType dt on dt.templateNodeId = TemplateNode.id 
			                            left outer join cmsContentType ct  on dt.contenttypenodeid = ct.nodeid
			                            left outer join cmscontent c on ct.nodeid = c.contenttype
			                            left outer join umbracoNode n on c.nodeid = n.id
                                        left outer join cmsDocument d on d.nodeId = n.id and d.newest = 1
			                            left outer join cmsTemplate ChildTemplate on Template.nodeId = ChildTemplate.master
                                        where template.nodeid = ?			                            
                                        group by Template.alias, template.nodeid, dt.IsDefault, 
                                                ct.alias , ct.nodeId, n.text, n.id, n.path, d.templateId
                                        ";
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@TemplateNodeId", TemplateDropDownList.SelectedValue); 
            DataTable dt = uDbCompare.QueryDb(templateQuery, connectionString, parameters);

            return dt;
        }

        protected void TemplateGridView_OnRowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataRowView drv = e.Row.DataItem as DataRowView;

                int templateNodeId = (int)drv["TemplateNodeId"];
                int? contentTypeNodeId = drv["ContentTypeNodeId"] as Int32?;
                int? contentNodeId = drv["ContentNodeId"] as Int32?;

                uDbCompareUI.SetLinkCell(uDbCompareUI.GetCellFromColumnName(e.Row, "TemplateNodeId"),
                    templateNodeId.ToString(), uDbCompare.GetLinkUrl(LinkItem.Template, templateNodeId, UseRemoteConnectionString));
                uDbCompareUI.SetLinkCell(uDbCompareUI.GetCellFromColumnName(e.Row, "ContentTypeNodeId"), 
                    contentTypeNodeId.ToString(), uDbCompare.GetLinkUrl(LinkItem.DocType, contentTypeNodeId, UseRemoteConnectionString));
                uDbCompareUI.SetLinkCell(uDbCompareUI.GetCellFromColumnName(e.Row, "ContentNodeId"), 
                     contentNodeId.ToString(), uDbCompare.GetLinkUrl(LinkItem.Content, contentNodeId, UseRemoteConnectionString));

                TableCell contentNodePathCell = uDbCompareUI.GetCellFromColumnName(e.Row, "ContentNodePath");
                if (contentNodePathCell.Text.StartsWith("-1,-20,"))
                    uDbCompareUI.SetCellColour(contentNodePathCell, DbCompareMatch.Missing);
            }
        }
    }
}